package com.zjr.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.zjr.db.DBConnection;
import com.zjr.domain.User;

public class DepartmentService {
	private Connection conn = null;
	private	PreparedStatement stmt = null;
	private ResultSet rs = null;
	private String sql = null;
	
	//获取每个部门的人数
	public HashMap<String, Integer> getDepartmentNum(){
		Map<String, Integer> departmentNum = new HashMap<String, Integer>();
		try {
			conn = DBConnection.getConnection();
			sql = "select count(*) from user u, department d where u.did=d.did and d.did=1";
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			if(rs.next()){
				departmentNum.put("技术部", rs.getInt(1));
			}
			
			sql = "select count(*) from user u, department d where u.did=d.did and d.did=2";
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			if(rs.next()){
				departmentNum.put("研发部", rs.getInt(1));
			}
			
			sql = "select count(*) from user u, department d where u.did=d.did and d.did=3";
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			if(rs.next()){
				departmentNum.put("人事部", rs.getInt(1));
			}
			
			sql = "select count(*) from user u, department d where u.did=d.did and d.did=4";
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			if(rs.next()){
				departmentNum.put("财务部", rs.getInt(1));
			}
			
			sql = "select count(*) from user u, department d where u.did=d.did and d.did=5";
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			if(rs.next()){
				departmentNum.put("行政部", rs.getInt(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBConnection.Close(rs, stmt, conn);
		}
		
		return (HashMap<String, Integer>) departmentNum;
	}
	
	//以部门id来获取部门成员
	public ArrayList<User> getUserByDid(int did){
		List<User> users = new ArrayList<User>();
		try {
			conn = DBConnection.getConnection();
			sql = "select * from user u, department d where d.did=? and u.did=d.did";
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, did);
			rs = stmt.executeQuery();
			while(rs.next()){
				User user = new User();
				user.setUid(rs.getInt("uid"));
				user.setUsername(rs.getString("username"));
				user.setPassword(rs.getString("password"));
				user.setDid(rs.getInt("did"));
				user.setPhone(rs.getString("phone"));
				user.setAddress(rs.getString("address"));
				user.setPosition(rs.getInt("position"));
				users.add(user);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBConnection.Close(rs, stmt, conn);
		}
		
		return (ArrayList<User>) users;
	}
}
